package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.bean.RecordBean;
import com.db.JdbcUtil;
import com.exception.BackException;
import com.exception.DBException;

public class ReturnDao
{
	private Connection con = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	private Statement stmt = null;
	public void backRecord(RecordBean rb) throws DBException, BackException
	{
		String recordID_sql = "select recordID,barcode from lab_record where backDate is null and barcode="
				+ "'" + rb.getBarcode() + "'";
		
		boolean flag = true;
		con = JdbcUtil.getConnection();
		try
		{
			// query the recordID
			// 1：confirm whether the record is exist.
			// 2 get the recordID, after the back, infomation should be return
			// to the view
			// the function getBackResult will use this recordID
			stmt = con.createStatement();
			rs = stmt.executeQuery(recordID_sql);
			while (rs.next())
			{
				// jutify whether the record is exist
				flag = false;
				int recordid = rs.getInt(1);
				String barcode = rs.getString(2);
				rb.setRecordID(recordid);
				// set backDate. make the instrument state to be in lab
				// transaction
				String sql_back = "update lab_record set backDate= curdate() where recordID ="
						+ recordid;
				String sql_in = "update lab_instrument set ifin='yes' where barcode="
						+ "'" + barcode + "'";
				try
				{
					con.setAutoCommit(false);
					stmt = con.createStatement();
					stmt.addBatch(sql_back);
					stmt.addBatch(sql_in);
					stmt.executeBatch();
					con.commit();
				}
				catch (SQLException e)
				{
					try
					{
						// rollback the transcation
						con.rollback();
						con.setAutoCommit(true);
					}
					catch (SQLException e1)
					{
						e1.printStackTrace();
					}
					throw new BackException("Fail to back this instrument");
				}
			}
			// user does not take the borrow
			if (flag)
			{
				throw new BackException("record is not exist");
			}
		}
		catch (SQLException e2)
		{
			throw new BackException("Fail to get the borrow record for user");
		}
		finally
		{
			JdbcUtil.free(rs, stmt, con);
		}
	}

	public void getBackResult(RecordBean recordBean) throws DBException,
			BackException

	{
		String sql = "select a.userID,d.barcode,a.firstname,a.lastname,a.gender,a.email,d.name,c.borrowDate,c.sbackDate,c.backDate from lab_user a join lab_record c on a.userID = c.userID  join lab_instrument d on d.barcode = c.barcode where c.recordID=?";
		con = JdbcUtil.getConnection();
		try
		{
			// get the user info
			ps = con.prepareStatement(sql);
			ps.setInt(1, recordBean.getRecordID());
			rs = ps.executeQuery();
			while (rs.next())
			{
				recordBean.setUserID(rs.getString(1));
				recordBean.setBarcode(rs.getString(2));
				recordBean.setFirstname(rs.getString(3));
				recordBean.setLastname(rs.getString(4));
				recordBean.setGender(rs.getString(5));
				recordBean.setEmail(rs.getString(6));
				recordBean.setInsName(rs.getString(7));
				recordBean.setBorrowDate(rs.getDate(8));
				recordBean.setSBackDate(rs.getDate(9));
				recordBean.setBackDate(rs.getDate(10));
			}
		}
		catch (SQLException e)
		{
			throw new BackException("Fail to get result of back infomation");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
	}
}
